import pandas as pd
from sqlalchemy import create_engine
import pymysql

if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/transportationdata?charset=utf8")
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='transportationdata', charset='utf8')
    # 车牌过滤
    plateData = pd.read_sql("select * from plate_table where plate_num!='-'", con=conn)
    # 路口过滤
    monitorData = pd.read_sql("select * from monitor_table WHERE monitor_na NOT LIKE '%反向%'", con=conn)
    plateData = plateData.merge(monitorData, on="monitor_id", how="left")
    plateData = plateData.dropna()

    dic = {}

    # 根据车牌分组
    for name, group in plateData.groupby("plate_num"):
        # 过滤数量不足的数据
        if len(group) <= 1:
            continue

        # 根据时间逆序
        group = group.sort_values(by="pass_time", ascending=False)

        # 顺序访问前后两行数据
        for i in range(1, len(group)):
            curData = group.iloc[i - 1]
            preData = group.iloc[i]

            cur = curData["monitor_id"]
            direction = curData["direction"]
            pre = preData["monitor_id"]
            # 前后路口一致，数据无意义
            if cur == pre:
                continue
            # 上下游路口流量计算
            key = (cur, direction, pre)
            dic[key] = dic.get(key, 0) + 1
    # 根据计数推测指定路口的指定方向的上游路口
    cols = ["cur", "direction", "pre", "count"]
    junctionRelationCountData = pd.DataFrame(columns=cols)
    for item in dic.items():
        series = pd.Series((item[0][0], item[0][1], item[0][2], item[1]), index=cols)
        junctionRelationCountData = junctionRelationCountData.append(series, ignore_index=True)
    cols = ["cur", "direction", "pre"]
    junctionRelationData = pd.DataFrame(columns=cols)
    for names, group in junctionRelationCountData.groupby(["cur", "direction"]):
        group["count"] = group["count"].astype("int")
        idx = group["count"].argmax()
        pre = group.iloc[idx]["pre"]
        series = pd.Series(([names[0], names[1], pre]), index=["cur", "direction", "pre"])
        print(series)
        junctionRelationData = junctionRelationData.append(series, ignore_index=True)

    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/transportationdata?charset=utf8")
    junctionRelationData.to_sql("junction_relation_table", con=engine, if_exists="append", index=False)